package com.android.nearshop.io.database;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.TreeMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.location.Location;
import android.os.AsyncTask;
import android.util.Log;

import com.android.nearshop.io.protocol.out.Shop;
import com.android.nearshop.io.protocol.out.ShopDetail;
import com.android.nearshop.util.Constant;
import com.android.nearshop.util.NotifyListener;

/*********************************************************
 * @author : zhaohua
 * @version : 2012-8-20
 * @see : 商家信息缓存表，只保存商家id唯一的信息，且只有查看详细的商家才保存
 * @Copyright : copyrights reserved by personal 2007-2011
 **********************************************************/
public class ShopDB extends DB
{
    private static final String TAG = "ShopDB";
    public static final String TABLE_NAME = "shop_cache";
    
    public static final String ID = "shopId";
    public static final String SHOP_NAME = "shopName";
    public static final String CATEGORY_NAME = "catalogyName";
    public static final String SHOP_ADDR = "shopAdd";
    public static final String SHOP_PHONE = "shopPhone";
    public static final String RECOMMEND = "recommend";
    public static final String LON = "lon";
    public static final String LAT = "lat";
    public static final String DISCOUNT_DETAIL = "discountdetail";
    public static final String CONTENT = "content";
    public static final String COMMENT_NUM = "commentNum";
    public static final String DISCOUNT = "discount";
    public static final String CLICKED_NUM = "clickedNum";
    public static final String SHOP_AVD_PIC = "shopAdvPic";
    public static final String WEEK_CONTENT = "weekContent";
    public static final String SCORE_COUNT = "scoreCount";
    public static final String TIME = "time";
    
    public static final String SQL_CREATE = "CREATE TABLE[" + TABLE_NAME + "] ("
            + "[" + ID + "] INTEGER NOT NULL," 
            + "[" + SHOP_NAME+ "] STRING NOT NULL, " 
            + "[" + CATEGORY_NAME + "] STRING," 
            + "[" + SHOP_ADDR + "] STRING," 
            + "[" + SHOP_PHONE + "] STRING,"
            + "[" + RECOMMEND + "] INTEGER,"
            + "[" + LON + "] FLOAT NOT NULL,"
            + "[" + LAT + "] FLOAT NOT NULL,"
            + "[" + DISCOUNT_DETAIL + "] STRING,"
            + "[" + CONTENT + "] STRING,"
            + "[" + DISCOUNT + "] STRING,"
            + "[" + COMMENT_NUM + "] INTEGER,"
            + "[" + CLICKED_NUM + "] INTEGER default (1),"
            + "[" + SHOP_AVD_PIC + "] STRING,"
            + "[" + WEEK_CONTENT + "] STRING,"
            + "[" + SCORE_COUNT + "] STRING,"
            + "[" + TIME + "] TIMESTAMP default (datetime('now', 'localtime')),"
            + "CONSTRAINT MEM_KEY UNIQUE("+ID+"));";

    private static int BASE_INDEX = 0;
    private static final int INDEX_ID = BASE_INDEX;
    private static final int INDEX_SHOP_NAME = ++BASE_INDEX;
    private static final int INDEX_CATEGORY_NAME = ++BASE_INDEX;
    private static final int INDEX_SHOP_ADDR = ++BASE_INDEX;
    private static final int INDEX_SHOP_PHONE = ++BASE_INDEX;
    private static final int INDEX_RECOMMEND = ++BASE_INDEX;
    private static final int INDEX_LON = ++BASE_INDEX;
    private static final int INDEX_LAT = ++BASE_INDEX;
    private static final int INDEX_DISCOUNT_DETAIL = ++BASE_INDEX;
    private static final int INDEX_CONTENT = ++BASE_INDEX;
    private static final int INDEX_DISCOUNT = ++BASE_INDEX;
    private static final int INDEX_COMMENT_NUM = ++BASE_INDEX;
    private static final int INDEX_CLICKED_NUM = ++BASE_INDEX;
    private static final int INDEX_SHOP_AVD_PIC = ++BASE_INDEX;
    private static final int INDEX_WEEK_CONTENT = ++BASE_INDEX;
    private static final int INDEX_SCORE_COUNT = ++BASE_INDEX;
    
    /**
     * 保存一个商家，如果已存在，则查看次数加1
     * @param shop
     * @return
     */
    public static boolean saveShop(ShopDetail shop)
    {
        SQLiteDatabase db = getDB(true);
        try
        {
            long id = db.insertOrThrow(TABLE_NAME, null, toContentValues(shop));
            Log.d(TAG, "saveShop = " + shop.shopId);
            return id > 0;
        }
        catch (Exception e)
        {
            com.android.nearshop.util.Log.e(TAG, "", e);
            // 说明已保存，则更新查看次数
            updateClickedNum(shop.shopId);
        }
        return false;
    }
    
    /**
     * 批量查找数据
     * @param section
     * @param orderBy
     * @return
     */
    public static List<ShopDetail> findBetch(String section, String orderBy, int max){
    	List<ShopDetail> shopDetails = new ArrayList<ShopDetail>();
    	SQLiteDatabase db = getDB(false);       
        Cursor c = null;
    	try
        {
            c = db.query(TABLE_NAME, null, section, null, null, null, orderBy, max + "");
            while(c.moveToNext())
            {
            	shopDetails.add(fill(c));
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (null != c)
            {
                c.close();
            }
        }
    	
    	return shopDetails;
    }
    
    /**
     * 由商家id查询商家详情
     * @param shopId
     * @return
     */
    public static ShopDetail find(int shopId)
    {
        SQLiteDatabase db = getDB(false);       
        Cursor c = null;
        String section = ID + "=" + shopId;
        try
        {
            c = db.query(TABLE_NAME, null, section, null, null, null, null);
            if(c.moveToNext())
            {
                return fill(c);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (null != c)
            {
                c.close();
            }
        }
        return null;
    }
    
    /**
     * 查找经纬度范围内的商家
     * @param range
     * @return
     */
    public static List<Shop> findRangeShop(Context cxt, Location loc){
    	Location dest = new Location("temp");
    	List<Shop> shops = new ArrayList<Shop>();
    	SQLiteDatabase db = getDB(false);
    	Cursor c1 = null;
    	Cursor c2 = null;
    	try{
    		c1 = db.query(TABLE_NAME, new String[]{"catalogyName", "COUNT(*)"}, null, null, "catalogyName", null, "COUNT(*) DESC");
    		String catalogString = null;
    		while(c1.moveToNext()){
    			catalogString = c1.getString(0);
    		  	c2 = db.query(TABLE_NAME, null, catalogString != null ? String.format("catalogyName = '%s'", catalogString) : null, null, null, null, null);
    	    	Shop shop = null;
    	    	while (c2.moveToNext()) {
    				double lat = c2.getDouble(INDEX_LAT);
    				double lon = c2.getDouble(INDEX_LON);
    				
    				dest.setLatitude(lat);
    				dest.setLongitude(lon);
    				double distance = loc.distanceTo(dest);
    				if(distance > 0 && distance < Constant.GUESS_LIKE_RANGE){
    					shop = new Shop();
    					shop.shopDetail2Shop(cxt, fill(c2));
    					shops.add(shop);
    				}
    			}
    	    	
    	    	if(c2 != null){
    				c2.close();
    			}
    	    	
    	    	if(shops.size() > 0){
    	    		break;
    	    	}
    		}
    		
	    	Collections.sort(shops);
    	}catch (Exception e) {
			com.android.nearshop.util.Log.w(TAG, "", e);
		} finally {
			if(c2 != null){
				c2.close();
			}
			
			if(c1 != null){
    			c1.close();
    			c1 = null;
    		}
		}
		
		return shops;
    }
    
    
    /**
     * 商家查看次数加1
     * @param shopid
     * @return
     */
    public static boolean updateClickedNum(int shopid)
    {
        String section = ID + "=" + shopid;
        String value = CLICKED_NUM + "=" + (CLICKED_NUM + "+1");
        return update(TABLE_NAME, value, section);
    }
    
    /**
     * 清除指定天数之前的缓存商家
     * @param day
     * @return
     */
    public static boolean clearPreNDay(int day)
    {
        String section = null;
        if(day > 0)
        {
            section =  TIME + " < datetime('now','-"+day+" day')";
        }
        return clear(TABLE_NAME, section);
    }
    
    public static void clearPreNDayAsyn(final int day,final NotifyListener<Boolean> listener)
    {
        new AsyncTask<Void, Void, Boolean>()
        {
            @Override
            protected Boolean doInBackground(Void... params)
            {
                return clearPreNDay(day);
            }
            
            protected void onPostExecute(Boolean result) 
            {
                if(listener != null)
                {
                    listener.onSucceed(result);
                }
            };
        }.execute();
    }
    
    public static ContentValues toContentValues(ShopDetail shop)
    {
        ContentValues cv = new ContentValues();
        
        cv.put(ID, shop.shopId);
        cv.put(SHOP_NAME, shop.shopName);
        cv.put(CATEGORY_NAME, shop.catalogyName);
        cv.put(SHOP_ADDR, shop.shopAdd);
        cv.put(SHOP_PHONE, shop.shopPhone);
        cv.put(RECOMMEND, shop.recommend);
        cv.put(LON, shop.lon);
        cv.put(LAT, shop.lat);
        cv.put(DISCOUNT_DETAIL, shop.discountdetail);
        cv.put(CONTENT, shop.content);
        cv.put(COMMENT_NUM, shop.commentNum);
        cv.put(DISCOUNT, shop.discount);

        cv.put(SHOP_AVD_PIC, shop.shopAdvPic);
        cv.put(SCORE_COUNT, shop.scoreCount[0] + "," + shop.scoreCount[1] + "," + shop.scoreCount[2]);
        cv.put(WEEK_CONTENT, shop.weekContent);
        
        return cv;
    }

    private static ShopDetail fill(Cursor cursor)
    {       
        ShopDetail shop = new ShopDetail();
        
        shop.shopId = cursor.getInt(INDEX_ID);
        shop.shopName = cursor.getString(INDEX_SHOP_NAME);
        shop.catalogyName = cursor.getString(INDEX_CATEGORY_NAME);
        shop.shopAdd = cursor.getString(INDEX_SHOP_ADDR);
        shop.shopPhone = cursor.getString(INDEX_SHOP_PHONE);
        shop.recommend= cursor.getInt(INDEX_RECOMMEND);
        shop.lon = cursor.getDouble(INDEX_LON);
        shop.lat = cursor.getDouble(INDEX_LAT);
        shop.discountdetail = cursor.getString(INDEX_DISCOUNT_DETAIL);
        shop.content = cursor.getString(INDEX_CONTENT);
        shop.commentNum = cursor.getInt(INDEX_COMMENT_NUM);
        shop.clickedNum = cursor.getInt(INDEX_CLICKED_NUM);
        shop.discount = cursor.getString(INDEX_DISCOUNT);
        
        //add by huangyue 增加新参数 
        shop.shopAdvPic = cursor.getString(INDEX_SHOP_AVD_PIC);
        shop.weekContent = cursor.getString(INDEX_WEEK_CONTENT);
        
        shop.scoreCount = new String[]{"0", "0", "0"}; 
        String scoreCountStr = cursor.getString(INDEX_SCORE_COUNT);
        if(scoreCountStr != null){
        	String[] scoreCounts = scoreCountStr.split(",");
        	try{
	        	for(int i = 0; i < scoreCounts.length; i++){
	        		shop.scoreCount[i] = scoreCounts[i];
	        	}
        	}catch (Exception e) {
				Log.w("ShopDetail", "", e);
			}
        }
        //end
        
        return shop;
    }
}
